# =======================================================================
# MySQLTableEntitySource.py
# KnowledgeEngine
#
# Created by John Meredith on 2006-10-10.
# Copyright (c) 2006 Faulkner Technologies. All rights reserved.
# =======================================================================

# =======================================================================
# KnowledgeEngine Library Imports
# =======================================================================
from Products.KnowledgeEngine.Utils import CallbackIterator
from Products.KnowledgeEngine.Common import *
from Products.KnowledgeEngine.Core import *
from Products.KnowledgeEngine.Entity import Entity
from Products.KnowledgeEngine.EntitySource import EntitySource, EntitySourceInfo
from Products.KnowledgeEngine.EventDefinition import *
from Products.KnowledgeEngine.PropertyDefinition import *
from Products.KnowledgeEngine.PropertyTypes import *
from Products.KnowledgeEngine.PropertyFormats import *
from Products.KnowledgeEngine.PropertyStructures import *
from Products.KnowledgeEngine.PermissionDefinition import *


# =======================================================================
# = Zope / Plone Library Imports                                        =
# =======================================================================
from Globals import InitializeClass
from AccessControl import ClassSecurityInfo
from App.ImageFile import ImageFile
from Products.CMFCore import permissions


# =======================================================================
# = Python Library Imports                                              =
# =======================================================================
from types import ListType


# =======================================================================
# = Grab all SQL connections / adapters defined within the Plone portal =
# =======================================================================
def getDatabaseConnections(context):
    """
        Returns a list of the ZMySQL database connections defined in
        the Plone portal
    """

    #we need to use the KE catalog to find the EntityChoosers
    ctool = getToolByName(context, 'SQLConnectionIDs', None)
    
    return ctool()


# =======================================================================
# = List all tables associated with the selected database association   =
# =======================================================================
def getDatabaseTables(context):
    """
        Returns a list of the tables defined in the chosen database
    """

    # Find the selected database connection based on the previously
    # selected property
    connection_id = context.getPropertyValue('sourceDatabase')
    if not connection_id:
        return [(None, None)]
    
    db_con = getToolByName(context, connection_id, None)
    if not db_con:
        return [(None, None)]
    else:
        db_con = db_con()

    # Query the database to the tables available
    tables = db_con.tables()
    
    return map(lambda t: (t['TABLE_NAME'], t['TABLE_NAME']), tables)


#####################################################################
## ModelEntitySourceInfo Class Definition
class MySQLTableEntity(Entity):
    """
        A MySQL table entity i.e. row
    """

    #---------------------------------------------------------------#
    # Declare security so ClassInfo may be accessed in DTML/ZPT etc
    security = ClassSecurityInfo()
    security.declareObjectPublic()
    security.setDefaultAccess("allow")

    def __getattr__(self, name):
        """ Allow entity.column_name access in templates etc. """
        
        # Data for a model is an assessment. Assume the 'name' is
        # a component identity and return it's value
        value = self._data.get(name)
        if value is not None:
            return value
        
        raise AttributeError, name
    


# =======================================================================
# = MySQLTableEntitySourceInfo Class Definition                         =
# =======================================================================
class MySQLTableEntitySourceInfo(EntitySourceInfo):
    """
        Defines the class info methods, defining information about
        the class without needing an instance of the class
    """


    #--------------------------------------------------------------------
    def getClassName(self):
        """
            Returns the name of the class for which
            we are providing information.
        """
        return 'MySQLTableEntitySource'


    #--------------------------------------------------------------------
    def getDisplayName(self):
        """
            Return a friendly name for the class instances
            (this is used for displaying in lists etc)
        """

        return "MySQLTableEntitySource"


    #--------------------------------------------------------------------
    def getDescription(self):
        """
            ABSTRACT: Return a friendly description on the purpose
            of this class.  This description is often used in the
            Property Editor for instances of this class
        """

        return "Defines a source for MySQL table rows as entities"


    #--------------------------------------------------------------------
    def getIdentityType(self):
        """
            Return the identity type.
        """

        return "esMySQLTable"


    #--------------------------------------------------------------------
    def getClassIcon(self):
        """
            Return the appropriate icon file for the class.
        """

        return ImageFile('skins/MySQLTableEntitySource_icon.png', globals())


    #--------------------------------------------------------------------
    def isContainer(self):
        """
            Returns if the instance is providing folderish/container
            services.

            Override an return true if the instance is to be
            a container for other instances.

            NOTE: all instances support "events" so there is no
            need to return true if you require "event" support.
        """

        return false


    #--------------------------------------------------------------------
    def isPermittedInContainer(self, containerClassInfo, containerInstance = None, instance=None):
        """
            ABSTRACT: Returns if class instances (instance) is permitted in
            the specified container (containerInstance)

            NOTE: This is from this class' instance perspective.
        """

        #CountryEntitySources don't contain anything
        return false


    #--------------------------------------------------------------------
    def isChildPermitted(self, childClassInfo, childInstance = None):
        """
            Returns if the specified child is permitted as a
            child of the class' instances.

            NOTE: This is from this class' instance perspective.

            For component containers, this always overrides what the
            return value from childClassInfo.isPermittedInContainer(...)
        """

        #CountryEntitySources don't contain anything
        return false


    #--------------------------------------------------------------------
    def isPortalObject(self):
        """
            Returns if the object can be placed directly in 
            a Plone Portal (rather than like most objects
            that must exist within a GenericObject container
            object.
            
            eg: Models, EntitySources are Portal Objects.
        """
        
        return true


    #--------------------------------------------------------------------
    def onConfigureProperties(self):
        """
            Automatically called by the Knowledge Engine once
            the class has been loaded.  Use this method to register
            class specific properties using the PropertyReflection interface.

            If this class is sub-classed, a call must be made to
            the super-class to ensure super-class properties are
            correctly registered

            NOTE: This method is called before ClassInfo.onAfterStartup()
        """

        #initialise the super-class properties for reflection
        EntitySourceInfo.onConfigureProperties(self)


        #add the 'entityNameSingular' property        
        self.addPropertyDefinition(
            PropertyDefinition(
                identity="entityNameSingular", 
                displayName="Singular Terminology", 
                description="What does a single row in the source MySQL table represent? (eg: Patient, Product, Assessment)",
                propertyType=pt_STRING,
                propertyFormat=pf_TEXT_SINGLELINE,
                defaultValue="Assessment",
                constructorEVAP=evap_READWRITE,
                propertyEditorEVAP=evap_READWRITE,
                isMandatory=true
            )
        )

        #add the 'entityNamePlural' property        
        self.addPropertyDefinition(
            PropertyDefinition(
                identity="entityNamePlural",
                displayName="Plural Terminology",
                description="The name for a collection of rows from the source MySQL table  (eg: Patients, Products, Assessments)",
                propertyType=pt_STRING,
                propertyFormat=pf_TEXT_SINGLELINE,
                defaultValue="Assessments",
                constructorEVAP=evap_READWRITE,
                propertyEditorEVAP=evap_READWRITE,
                isMandatory=true
            )
        )

        #add the 'databaseConnection' property        
        self.addPropertyDefinition(
            PropertyDefinition(
                identity="sourceDatabase",
                displayName="MySQL Database Connection",
                description="The ZMySQL database connection object",
                propertyType=pt_STRING,
                propertyFormat=pf_NATIVE,
                defaultValue=None,
                hasEnumeratedValues=true,
                enumeratedValuesFunction=getDatabaseConnections,
                constructorEVAP=evap_READWRITE,
                propertyEditorEVAP=evap_READWRITE,
                isMandatory=true,
            )
        )

        #add the 'databaseTable' property        
        self.addPropertyDefinition(
            PropertyDefinition(
                identity="sourceTable", 
                displayName="Entity Source Table", 
                description="The table to be used at the entity row source", 
                propertyType=pt_STRING, 
                propertyFormat=pf_NATIVE, 
                defaultValue=None,
                hasEnumeratedValues=true,
                enumeratedValuesFunction=getDatabaseTables,
                constructorEVAP=evap_READWRITE, 
                propertyEditorEVAP=evap_READWRITE,
                isMandatory=true,
            )
        )

        #add the 'entityNamePlural' property        
        self.addPropertyDefinition(
            PropertyDefinition(
                identity="sourceKey",
                displayName="Entity Source Key",
                description="The key / identity used to distinguish entities / rows",
                propertyType=pt_STRING,
                propertyFormat=pf_TEXT_SINGLELINE,
                defaultValue="id",
                constructorEVAP=evap_READWRITE,
                propertyEditorEVAP=evap_READWRITE,
                isMandatory=true
            )
        )


    #--------------------------------------------------------------------
    def onConfigureEvents(self):
        """
            Automatically called by the Knowledge Engine once
            the class has been loaded.  Use this method to register
            class specific events using the EventReflection interface.

            If this class is sub-classed, a call must be made to
            the super-class to ensure super-class properties are
            correctly registered

            NOTE: This method is called before ClassInfo.onAfterStartup()
        """

        #initialise the super-class events for reflection
        EntitySourceInfo.onConfigureEvents(self)


    #--------------------------------------------------------------------
    def onConfigurePermissions(self):
        """
            Automatically called by the Knowledge Engine once
            the class has been loaded.  Use this method to register
            class specific permission definitions.

            If this class is sub-classed, a call must be made to
            the super-class to ensure super-class properties are
            correctly registered

            NOTE: This method is called before ClassInfo.onAfterStartup()
        """

        #initialise the super-class permissions for reflection
        EntitySourceInfo.onConfigurePermissions(self)



# =======================================================================
# = MySQLTableEntitySource Class                                        =
# =======================================================================
class MySQLTableEntitySource(EntitySource):
    """
        An entity source which provides MySQL DB table row entities
    """

    security = ClassSecurityInfo()


    #--------------------------------------------------------------------
    # ABSTRACT TRANSIENT: a ClassInfo implementation instance to provide
    #                     info about this class
    _CLASSINFO = MySQLTableEntitySourceInfo()


    #--------------------------------------------------------------------
    # Default Attribute Values
    meta_type = 'MySQLTableEntitySource'

    
    #--------------------------------------------------------------------
    def __init__(self, id, identity):
        """
            Constructor for the object
        """

        EntitySource.__init__(self, id, identity)

        #perform onAfterInstanciation
        self.onAfterInstanciation()


    #--------------------------------------------------------------------
    def onAfterInstanciation(self):
        """
            Handle Knowledge Engine specific actions after this object
            has been loaded into memory.

            This is useful for performing backwards compatibility
        """

        #perform default onAfterInstanciation for the container
        EntitySource.onAfterInstanciation(self)


    #--------------------------------------------------------------------
    def getSourceDatabase(self):
        """
            Returns the selected database connection object (ZMySQLDA)
        """

        db_con = getToolByName(self, self.sourceDatabase, None)

        return db_con()


    #--------------------------------------------------------------------
    def getSourceTable(self):
        """
            Returns the name of the entity source table
        """
        
        return self.sourceTable


    #--------------------------------------------------------------------
    def getSourceKey(self):
        """
            Returns the key (unique identitfier) used to differentiate
            entities / rows
        """

        return self.sourceKey


    #--------------------------------------------------------------------
    def qstr(string, quote=True):
        """ 
            Wrapper function to quote a string for safe MySQL use
        """
        if string is None: return "NULL"
    
        if quote:
            return "'%s'" % MySQLdb.escape_string(str(string))
        else:
            return MySQLdb.escape_string(str(string))


    #--------------------------------------------------------------------
    def getNrEntities(self, user = None, filterBy = {}):
        """
            Returns the number of rows/entities contained in the table for
            the specified user.
            
            NOTE: user is not used at all
            
            The filterBy is a dict (key / value) used as a simple filter.
            TODO: Filtering needs to be reworked
        """

        # Remove authenticatedUser from filterBy if it's present, as it has
        filterBy.pop('authenticatedUser', None)

        criterea = [1]
        if len(filterBy) > 0:
            for (column, value) in filterBy.items():
                criterea += [ "%s='%s'" % (column, value) ]
        whereClause = ' AND '.join(map(lambda x: str(x), criterea))

        sql = "SELECT COUNT(*) FROM %s WHERE %s" % (self.getSourceTable(), whereClause)

        db_con = self.getSourceDatabase()
        description, result = db_con.query(sql)

        if result:
            return result[0][0]

        return 0


    #--------------------------------------------------------------------
    def getEntities(self, user = None, filterBy = {}):
        """
            Returns the entities from this entity source,
            ordered by entity identity.
            
            The filterBy is a dict (key / value) used as a simple filter.
            TODO: Filtering needs to be reworked
        """

        # Remove authenticatedUser from filterBy if it's present, as it has
        filterBy.pop('authenticatedUser', None)

        criterea = [1]
        if len(filterBy) > 0:
            for (column, value) in filterBy.items():
                criterea += [ "%s='%s'" % (column, value) ]
        whereClause = ' AND '.join(map(lambda x: str(x), criterea))

        sql = "SELECT * FROM %s WHERE %s ORDER BY %s" % (self.getSourceTable(), whereClause, self.getSourceKey())

        db_con = self.getSourceDatabase()
        description, result = db_con.query(sql, max_rows=0)

        # Iterate through the results creating a column/value dictionary
        # as the 
        entities = []
        for row in result:
            record = {}
            for item in map(lambda a,b: {a['name'] : b}, description, row):
                record.update(item)

            # Make sure we return an entity
            entity = MySQLTableEntity(
                record[self.getSourceKey()],
                record[self.getSourceKey()],
                data = record,
            )

            entities.append(entity)

        return entities


    #--------------------------------------------------------------------
    def getEntitiesByBatch(self, batchNr, batchSize = 10, user = None, filterBy = {}):
        """
            Returns a batch of entities as specified.
            
            NOTE 1: The first available batch is 0
            
            NOTE 2: The entities are returned in order of their name
            
            The filterBy is a dict (key / value) used as a simple filter.
            TODO: Filtering needs to be reworked
        """

        # Remove authenticatedUser from filterBy if it's present, as it has
        filterBy.pop('authenticatedUser', None)

        # Construct where clause of query from filterBy if present
        criterea = [1]
        if len(filterBy) > 0:
            for (column, value) in filterBy.items():
                criterea += [ "%s='%s'" % (column, value) ]
        whereClause = ' AND '.join(map(lambda x: str(x), criterea))

        sql = "SELECT * FROM %s WHERE %s ORDER BY %s LIMIT %d, %d" % (self.getSourceTable(), whereClause, self.getSourceKey(), batchNr * batchSize, batchSize)

        db_con = self.getSourceDatabase()
        description, result = db_con.query(sql, max_rows=0)

        # Iterate through the results creating a column/value dictionary
        # as the 
        entities = []
        for row in result:
            record = {}
            for item in map(lambda a,b: {a['name'] : b}, description, row):
                record.update(item)

            # Make sure we return an entity
            entity = MySQLTableEntity(
                record[self.getSourceKey()],
                record[self.getSourceKey()],
                data = record
            )

            entities.append(entity)

        return entities


    #--------------------------------------------------------------------
    def getEntity(self, entityIdentity, user = None):
        """
            Returns the entity with the specified identity
            for the provided user
        """

        entity = None

        # Make sure we actually have something
        if entityIdentity:
            # Convert to string
            if type(entityIdentity) is ListType:
                entityIdentity = ','.join(entityIdentity)
            
            # Query the database
            db_con = self.getSourceDatabase()
            sql = "SELECT * FROM %s WHERE %s in ('%s')" % (self.getSourceTable(), self.getSourceKey(), entityIdentity)
            
            description, result = db_con.query(sql)

            # Convert the results into a dict
            if len(result):
                retValue = {}
                for item in map(lambda a,b: {a['name'] : b}, description, result[0]):
                    retValue.update(item)

                # Make sure we return an entity
                entity = MySQLTableEntity(entityIdentity, entityIdentity, data = retValue)
        
        return entity



    #--------------------------------------------------------------------
    def getEntityNameSingular(self):
        """
            ABSTRACT: Return the name of a single entity provided 
            by the Entity Source
        """
        
        return self.entityNameSingular


    #--------------------------------------------------------------------
    def getEntityNamePlural(self):
        """
            ABSTRACT: Return the name of a collection of entities provided 
            by the Entity Source
        """
        
        return self.entityNamePlural


# =======================================================================
# = CMF Required Class Initialisation                                   =
# =======================================================================
def addMySQLTableEntitySource(self, id, REQUEST=None):
    """
        The Zope/CMF factory method to create an instance
    """

    #create a KnowledgeEngine identity for the new model
    identity = generateIdentity(MySQLTableEntitySource.identity_type)

    #create an instance
    object = MySQLTableEntitySource(id, identity)

    #add the object to the specified container (called self)
    self._setObject(id, object)

    if REQUEST is not None:
        REQUEST['RESPONSE'].redirect(object.absolute_url() + '/manage_main' )


#the CMF/Plone factory type information - used to create objects!)
factoryTypeInformation = {
    'id'                    : 'MySQLTableEntitySource',
    'meta_type'             : 'MySQLTableEntitySource',
    'description'           : "A KnowledgeEngine MySQLTableEntitySource",
    'icon'                  : 'MySQLTableEntitySource_icon.png',
    'product'               : 'KnowledgeEngine',
    'factory'               : 'addMySQLTableEntitySource',
    'filter_content_types'  : 0,
    'global_allow'          : 0,
    'immediate_view'        : 'view',
    'actions'               : (
                                {
                                    'id'            : 'view',
                                    'name'          : 'View',
                                    'action'        : 'string:${folder_url}/view',
                                    'permissions'   : (permissions.View,),
                                    'category'      : 'folder',
                                },
                              ),
}


# =======================================================================
# = Register Class Information with the KnowledgeEngine                 =
# =======================================================================
registerPortalObjectClass(MySQLTableEntitySource, addMySQLTableEntitySource, factoryTypeInformation)

